{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 数据库（Database）\n",
    "\n",
    "#### 什么是数据库\n",
    "\n",
    "数据库是由许多相关数据构成的集合。\n",
    "\n",
    "当用户或者应用需要访问和使用这些数据库中的数据时，需要借助专门的管理软件系统，也就是数据库管理系统（DBMS Database Management System）。按照数据的组织和管理模式，主要的 DBMS 可以分为关系数据库管理系统和非关系数据库管理系统。\n",
    "\n",
    "\n",
    "- 关系数据库管理系统（RDBMS Relational DBMS）主要使用二维表（Table）来存储数据，类似于 Excel 中的电子表格。表中的行对应一个实体或对象，列对应实体的属性。关系数据库使用标准的结构化查询语句（SQL）执行各种数据的增删改查以及数据库的管理操作。主流的关系数据库包括 Oracle、MySQL、SQL Server 以及 PostgreSQL 等。\n",
    "\n",
    "- 非关系数据管理系统（NoSQL）通常不支持关系模型，也不提供 SQL 接口。它们通常是为了解决关系数据库在某些场景下的局限性，例如大数据、横向可扩展性等。其中，NoSQL 代表 Not Only SQL。常见的 NoSQL 数据库包括文档数据库（MongoDB 等）、键值存储（Redis 等）、以及图数据库（Neo4j 等）。\n",
    "\n",
    "\n",
    "MySQL 是最流行的开源关系数据库管理系统，由 Oracle 公司进行开发并提供支持，提供了原生的文档数据库（JSON）支持。官网地址： https://www.mysql.com/\n",
    "\n",
    "MySQL 支持各种平台，包括 Windows、Linux 以及 macOS。相对于其他大型的数据库系统（Oracle、SQL Server 等）而言，MySQL 更加容易管理和使用，同时又具有非常好的性能、可靠性和扩展性。\n",
    "\n",
    "\n",
    "SQL 代表结构化查询语言（Structured Query Language），它是管理和访问关系数据库的标准语言。通过 SQL 可以执行数据的增加（Create）、删除（Delete）、修改（Update）以及查询（Retrieve），同时还可以执行许多数据库的管理操作。\n",
    "\n",
    ">A SQL query is a question you ask the database. If any of the data in the database satisfies the conditions of your query, SQL retrieves that data.\n",
    "\n",
    "`SELECT * FROM EMPLOYEE WHERE Age > 40 OR Salary > 100000`\n",
    "\n",
    "If in case MySQL is not available, you might use MariaDB instead. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 学习 MYSQL 数据库，建议方法：\n",
    "\n",
    "1. 找几本权威的数据库图书系统地学习一下，当然可以选择你感兴趣的部分，常用的数据库知识并不多；\n",
    "2. 自己安装一个数据库进行操作、实践。\n",
    "\n",
    "\n",
    "建议你可以在阿里云上尝试注册并且使用MySQL\n",
    "https://www.aliyun.com/product/rds/mysql\n",
    "\n",
    "网上资料\n",
    "\n",
    "MySQL指南\n",
    "1. 菜鸟教程 https://www.runoob.com/mysql/mysql-tutorial.html\n",
    "2. 知乎 https://www.zhihu.com/column/mysql-tutorial\n",
    "3. MySQL数据库学习宝典 http://c.biancheng.net/mysql/\n",
    "4. B站：黑马程序员 \n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Download\n",
    "\n",
    "MySQL  USTC 中科大的镜像 [点击前往](http://mirrors.ustc.edu.cn/mysql-ftp/Downloads/)\n",
    "\n",
    "注意根据你的操作系统选择合适的镜像文件 ！\n",
    "\n",
    "MariaDB 开源软件 可以到其官网下载 \n",
    "\n",
    "https://downloads.mariadb.org/\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 安装MariaDB/MySQL\n",
    "\n",
    "\n",
    "Windows10系统下安装MariaDB 的教程图解 [Click](https://pcedu.pconline.com.cn/1367/13676123.html)\n",
    "\n",
    "Windows10 MYSQL Installer 安装 [Click](https://www.runoob.com/w3cnote/windows10-mysql-installer.html)\n",
    "\n",
    "注意：在安装过程中要设置root用户的密码，请记住并且妥善保管这个密码，在后续查看及调用数据库的时候都需要使用这个重要的密码。而且root用户是具有最高权限的，不能泄露该密码。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### mysql\n",
    "Enter password：***  \n",
    "mysql>use mysql;   \n",
    "Database changed   \n",
    "mysql> quit; \n",
    "#### 如果需要修改密码，可以根据以下的方法\n",
    "mysql 修改密码：\n",
    "ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "  # 导入模块中的create_engine，需要利用它来进行连接数据库\n",
    "  #     pip install sqlalchemy\n",
    "  #     https://www.osgeo.cn/sqlalchemy/core/engines.html\n",
    "\n",
    "# pw = input('Please provide the password:')\n",
    "# 123456\n",
    "# db = input('Please input the name of the database which you would like to access:')\n",
    "# test\n",
    "# engine = create_engine('mysql+pymysql://root:{}@127.0.0.1:3306/{}?charset=utf8'.format(pw,db))\n",
    "engine = create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8')\n",
    "  # 结果：Engine(mysql+pymysql://root:***@127.0.0.1:3306/test?charset=utf8)\n",
    "  # dialect    +driver     ://username:   password  @host  :   port /database\n",
    "  # \"数据库类型+数据库驱动://数据库用户名:数据库密码@IP地址:  端口号/数据库?编码...      \", 其它参数\n",
    "  # mysql      +pymysql   ://root:        ***      @127.0.0.1:3306  /test  ?charset=utf8)\n",
    "  #     （1）dialect：方言：sqlite、mysql、postgresql、oracle 或 mssql \n",
    "  #     （2）drivername：是要使用所有小写字母连接到数据库的DBAPI的名称\n",
    "  #                      DB-API 定义:\n",
    "  #                      1.我们有时会想与我们的数据库进行交互,并在特定的编程语言中使用它的结果。\n",
    "  #                      2.以特定语言(如 Ruby、Python、JavaScript 等)构建 Web 应用程序或数据管道。\n",
    "  #     （3）host：127.0.0.1 代表本机，是特指本机的IP地址\n",
    "  #     （4）port：3306是数据库使用的端口号\n",
    "  #     （5）utf8是编码，一般有中文就应该使用utf8"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "  # Write to database\n",
    "dat = pd.read_csv('stockdata.csv')\n",
    "dat.head()\n",
    "\n",
    "dat.to_sql(name='stockdata', con=engine, if_exists='append', index=False)\n",
    "  # mysqldb是python2的mysql连接库，在python3时，已经废除mysqldb，改为pymysql。\n",
    "  #   在sqlachemy必须使用mysqldb驱动时，需要先导入pymysql，然后pymysql.install_as_MySQLdb()才能使用。\n",
    "  # name：指定的是将输入接入数据库当中的哪个表\n",
    "  # con：与数据库链接的方式，推荐使用sqlalchemy的engine类型\n",
    "  # schema: 相应数据库的引擎，不设置则使用数据库的默认引擎，如mysql中的innodb引擎\n",
    "  # if_exists: 当数据库中已经存在数据表时对数据表的操作，有replace替换、append追加，\n",
    "  #            fail则当表存在时提示ValueError。\n",
    "  # index：对DataFrame的index索引的处理，为True时索引也将作为数据写入数据表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "### 从本地文件夹读取数据文件\n",
    "import pandas as pd\n",
    "  # Write to database\n",
    "path = 'F:\\\\hongdou\\\\1111111111111111课程\\\\Python\\\\python_course-master\\\\DataSession\\\\stockdata.csv'\n",
    "path = 'F:/hongdou/1111111111111111课程/Python/python_course-master/DataSession/stockdata.csv'\n",
    "  #（1）可行。path = 'F:\\\\hongdou\\\\1111111111111111课程\\\\Python\\\\python_course-master\\\\DataSession\\\\stockdata.csv'\n",
    "  #（2）可行。path2 = 'F:/hongdou/1111111111111111课程/Python/python_course-master/DataSession/stockdata.csv'\n",
    "  #（3）不对。有特殊字符无法用 \\ 读取。\n",
    "  #     path = 'F:\\hongdou\\1111111111111111课程\\Python\\python_course-master\\DataSession\\stockdata.csv'\n",
    "  # \\,/,\\\\对比。https://blog.csdn.net/qq_33726635/article/details/103961952\n",
    "dat = pd.read_csv(path)\n",
    "\n",
    "dat.to_sql(name='stockdata', con=engine, if_exists='append', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>vol</th>\n",
       "      <th>close</th>\n",
       "      <th>low</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>285487.23</td>\n",
       "      <td>16.20</td>\n",
       "      <td>15.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>332693.07</td>\n",
       "      <td>16.51</td>\n",
       "      <td>16.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>184201.74</td>\n",
       "      <td>17.95</td>\n",
       "      <td>16.90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>226621.76</td>\n",
       "      <td>17.21</td>\n",
       "      <td>16.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>320102.62</td>\n",
       "      <td>16.97</td>\n",
       "      <td>15.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>189263.20</td>\n",
       "      <td>16.35</td>\n",
       "      <td>15.18</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         vol  close    low\n",
       "0  285487.23  16.20  15.56\n",
       "1  332693.07  16.51  16.24\n",
       "2  184201.74  17.95  16.90\n",
       "3  226621.76  17.21  16.77\n",
       "4  320102.62  16.97  15.50\n",
       "5  189263.20  16.35  15.18"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql_cmd = \"select vol, close, low from stockdata where vol > 100000 and close > 16\"\n",
    "\n",
    "# Read from Database\n",
    "df = pd.read_sql(sql=sql_cmd, con=engine)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>trade_date</th>\n",
       "      <th>ts_code</th>\n",
       "      <th>open</th>\n",
       "      <th>high</th>\n",
       "      <th>low</th>\n",
       "      <th>close</th>\n",
       "      <th>pre_close</th>\n",
       "      <th>change</th>\n",
       "      <th>pct_chg</th>\n",
       "      <th>vol</th>\n",
       "      <th>amount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>20200729</td>\n",
       "      <td>002747.SZ</td>\n",
       "      <td>15.58</td>\n",
       "      <td>16.69</td>\n",
       "      <td>15.40</td>\n",
       "      <td>16.53</td>\n",
       "      <td>15.60</td>\n",
       "      <td>0.93</td>\n",
       "      <td>5.9615</td>\n",
       "      <td>187518.29</td>\n",
       "      <td>300984.436</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>20200728</td>\n",
       "      <td>002747.SZ</td>\n",
       "      <td>15.68</td>\n",
       "      <td>15.98</td>\n",
       "      <td>15.22</td>\n",
       "      <td>15.60</td>\n",
       "      <td>15.38</td>\n",
       "      <td>0.22</td>\n",
       "      <td>1.4304</td>\n",
       "      <td>125663.79</td>\n",
       "      <td>195823.200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20200727</td>\n",
       "      <td>002747.SZ</td>\n",
       "      <td>16.15</td>\n",
       "      <td>16.16</td>\n",
       "      <td>15.15</td>\n",
       "      <td>15.38</td>\n",
       "      <td>16.20</td>\n",
       "      <td>-0.82</td>\n",
       "      <td>-5.0617</td>\n",
       "      <td>228584.90</td>\n",
       "      <td>355934.528</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>20200724</td>\n",
       "      <td>002747.SZ</td>\n",
       "      <td>16.80</td>\n",
       "      <td>16.81</td>\n",
       "      <td>15.56</td>\n",
       "      <td>16.20</td>\n",
       "      <td>16.51</td>\n",
       "      <td>-0.31</td>\n",
       "      <td>-1.8776</td>\n",
       "      <td>285487.23</td>\n",
       "      <td>460908.718</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20200723</td>\n",
       "      <td>002747.SZ</td>\n",
       "      <td>17.80</td>\n",
       "      <td>18.07</td>\n",
       "      <td>16.24</td>\n",
       "      <td>16.51</td>\n",
       "      <td>17.95</td>\n",
       "      <td>-1.44</td>\n",
       "      <td>-8.0223</td>\n",
       "      <td>332693.07</td>\n",
       "      <td>561431.842</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   trade_date    ts_code   open   high    low  close  pre_close  change  \\\n",
       "0    20200729  002747.SZ  15.58  16.69  15.40  16.53      15.60    0.93   \n",
       "1    20200728  002747.SZ  15.68  15.98  15.22  15.60      15.38    0.22   \n",
       "2    20200727  002747.SZ  16.15  16.16  15.15  15.38      16.20   -0.82   \n",
       "3    20200724  002747.SZ  16.80  16.81  15.56  16.20      16.51   -0.31   \n",
       "4    20200723  002747.SZ  17.80  18.07  16.24  16.51      17.95   -1.44   \n",
       "\n",
       "   pct_chg        vol      amount  \n",
       "0   5.9615  187518.29  300984.436  \n",
       "1   1.4304  125663.79  195823.200  \n",
       "2  -5.0617  228584.90  355934.528  \n",
       "3  -1.8776  285487.23  460908.718  \n",
       "4  -8.0223  332693.07  561431.842  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 先将数据放进数据库\n",
    "dat2 = pd.read_csv('multi_stock_data.csv')\n",
    "dat2.to_sql(name='multi_stock_data', con=engine, if_exists='append', index=False)\n",
    "\n",
    "# 从数据库读写数据\n",
    "engine = create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8')\n",
    "sql_cmd = \"select * from multi_stock_data where ts_code like '002747%%' and close > 13\"\n",
    "df = pd.read_sql(sql=sql_cmd, con=engine)\n",
    "  # read_sql（）：从数据库读写数据\n",
    "df.head()\n",
    "  # https://blog.csdn.net/qq_42874916/article/details/120187768"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "### 从本地文件夹读取数据文件\n",
    "# 先将数据放进数据库\n",
    "path = 'F:\\\\hongdou\\\\1111111111111111课程\\\\Python\\\\python_course-master\\\\DataSession\\\\multi_stock_data.csv'\n",
    "dat = pd.read_csv(path)\n",
    "dat.to_sql(name='multi_stock_data', con=engine, if_exists='append', index=False)\n",
    "\n",
    "# 从数据库读写数据\n",
    "engine = create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8')\n",
    "sql_cmd = \"select * from multi_stock_data where ts_code like '002747%%' and close > 13\"\n",
    "df = pd.read_sql(sql=sql_cmd, con=engine)\n",
    "  # read_sql（）：从数据库读写数据\n",
    "df.head()\n",
    "  # https://blog.csdn.net/qq_42874916/article/details/120187768"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import mysql.connector\n",
    "  # exit() or quit() 先退出Python\n",
    "  #   https://blog.csdn.net/weixin_43094275/article/details/107393385\n",
    "  # pip install mysql-connector\n",
    "  # mysql-connector是一个Python模块，它在Python中重新实现MySQL协议，它比较慢，但不需要C库，因此更便携。\n",
    "conn = mysql.connector.connect(host= '127.0.0.1',port=3306,user=\"root\",\\\n",
    "                               password = '123456' ,database = 'news' ,charset=\"utf8\")\n",
    "cur = conn.cursor() \n",
    "  # 返回游标对象\n",
    "sql = \"select * from sina_fin_news where news like '%%澳新银行%%'\"\n",
    "cur.execute(sql)\n",
    "  # 通过游标调用execute()方法执行SQL语句\n",
    "result = cur.fetchall()\n",
    "  # fetchall()，从查询结果中获取所有数据\n",
    "  # fetchone()，从查询结果中获取1行数据\n",
    "  # fetchmany(n)，从查询结果中获取n行数据\n",
    "result[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "### 先将数据放进mySQL\n",
    "from sqlalchemy import create_engine\n",
    "import pandas as pd\n",
    "engine = create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/news?charset=utf8')\n",
    "# path ='C:\\\\Users\\\\ibm\\\\Data Session\\\\sina_fin_news.csv'\n",
    "path = 'F:\\\\hongdou\\\\1111111111111111课程\\\\Python\\\\python_course-master\\\\database\\\\sina_fin_news.csv'\n",
    "dat2 = pd.read_csv(path)\n",
    "\n",
    "dat = pd.read_csv(r'C:\\Users\\ibm\\Data Session\\sina_fin_news.csv',encoding='ISO-8859-1')\n",
    "dat.to_sql(name='sina_fin_news', con=engine, if_exists='append', index=False)\n",
    "import pandas as pd\n",
    "dat.to_sql(name='sina_fin_news', con=engine, if_exists='append', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 用pymysql连接MySQL数据库\n",
    "1、安装模块：pymysql\n",
    "\n",
    "(1)`exit()` or `quit()` 先退出Python。(2)安装模块`pip install pymysql`。\n",
    "\n",
    "https://blog.csdn.net/weixin_43094275/article/details/107393385\n",
    "\n",
    "2、启动mysql数据库\n",
    "\n",
    "3、连接数据库：`connect()`，返回一个对象\n",
    "\n",
    "`con = pymysql.connect(user,passwd,port,host,db,charset)`：\n",
    "    （1）user：数据库用户\n",
    "    （2）passwd：数据库登录密码\n",
    "    （3）host：数据库服务器地址\n",
    "    （4）db：要连接的database\n",
    "    （5）port：端口号，默认为3306\n",
    "    （6）charset：字符集\n",
    "\n",
    "`connect()`返回对象常用的方法：\n",
    "    `cursor()`：创建游标\n",
    "    `commit()`：提交事务\n",
    "    `rollback()`：回滚事务\n",
    "    `close()`：关闭数据库连接\n",
    "    \n",
    "————————————————\n",
    "版权声明：本文为CSDN博主「佛系的老肖」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。\n",
    "原文链接：https://blog.csdn.net/a883774913/article/details/125313067"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "21\n",
      "((20200729, u'002747.SZ', 15.58, 16.69, 15.4, 16.53, 15.6, 0.93, 5.9615, 187518.29, 300984.436), (20200728, u'002747.SZ', 15.68, 15.98, 15.22, 15.6, 15.38, 0.22, 1.4304, 125663.79, 195823.2), (20200727, u'002747.SZ', 16.15, 16.16, 15.15, 15.38, 16.2, -0.82, -5.0617, 228584.9, 355934.528), (20200724, u'002747.SZ', 16.8, 16.81, 15.56, 16.2, 16.51, -0.31, -1.8776, 285487.23, 460908.718), (20200723, u'002747.SZ', 17.8, 18.07, 16.24, 16.51, 17.95, -1.44, -8.0223, 332693.07, 561431.8420000001), (20200722, u'002747.SZ', 17.12, 18.18, 16.9, 17.95, 17.21, 0.74, 4.2998, 184201.74, 325956.565), (20200721, u'002747.SZ', 17.2, 17.87, 16.77, 17.21, 16.97, 0.24, 1.4143, 226621.76, 390465.48600000003), (20200720, u'002747.SZ', 15.8, 16.97, 15.5, 16.97, 15.43, 1.54, 9.9806, 320102.62, 530137.9920000001), (20200717, u'002747.SZ', 14.86, 15.8, 14.8, 15.43, 14.87, 0.56, 3.766, 199292.16, 305435.914), (20200716, u'002747.SZ', 14.95, 16.1, 14.83, 14.87, 15.23, -0.36, -2.3638, 229290.8, 353556.481), (20200715, u'002747.SZ', 15.4, 15.6, 14.8, 15.23, 15.2, 0.03, 0.1974, 213356.58, 323258.908), (20200714, u'002747.SZ', 15.21, 16.16, 14.68, 15.2, 15.32, -0.12, -0.7833, 222269.86, 340759.69700000004), (20200713, u'002747.SZ', 14.97, 15.43, 14.75, 15.32, 15.12, 0.2, 1.3228, 184452.45, 278886.69800000003), (20200710, u'002747.SZ', 15.63, 15.68, 14.6, 15.12, 15.5, -0.38, -2.4516, 218210.43, 329484.318), (20200709, u'002747.SZ', 14.2, 15.5, 14.01, 15.5, 14.09, 1.41, 10.0071, 267772.91, 395199.48600000003), (20200708, u'002747.SZ', 13.03, 14.4, 13.03, 14.09, 13.09, 1.0, 7.6394, 293345.03, 407198.381), (20200707, u'002747.SZ', 13.05, 13.55, 12.92, 13.09, 13.0, 0.09, 0.6923, 184150.75, 243893.075), (20200224, u'002747.SZ', 13.3, 13.62, 13.08, 13.53, 13.15, 0.38, 2.8897, 206334.56, 277038.776), (20200221, u'002747.SZ', 13.44, 13.87, 13.03, 13.15, 13.61, -0.46, -3.3799, 254587.72, 340625.965), (20200220, u'002747.SZ', 13.46, 13.75, 13.19, 13.61, 13.52, 0.09, 0.6657, 197563.81, 265874.859), (20200219, u'002747.SZ', 12.82, 14.03, 12.6, 13.52, 12.82, 0.7, 5.4602, 286537.16, 383271.05700000003))\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(20200729,\n",
       " u'002747.SZ',\n",
       " 15.58,\n",
       " 16.69,\n",
       " 15.4,\n",
       " 16.53,\n",
       " 15.6,\n",
       " 0.93,\n",
       " 5.9615,\n",
       " 187518.29,\n",
       " 300984.436)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy import create_engine\n",
    "import pandas as pd\n",
    "import pymysql\n",
    "# 连接数据库\n",
    "conn = pymysql.connect(host= '127.0.0.1',port=3306,user='root',\\\n",
    "                       password = '123456' ,database = 'test' ,charset='utf8')\n",
    "cur = conn.cursor() \n",
    "  # 返回游标对象\n",
    "sql = \"select * from multi_stock_data where ts_code like '002747%%' and close > 13\"\n",
    "\n",
    "n = cur.execute(sql)\n",
    "  # 通过游标调用execute()方法执行SQL语句\n",
    "print(n)\n",
    "result = cur.fetchall()\n",
    "  # fetchall()，从查询结果中获取所有数据\n",
    "print(result)\n",
    "result[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "### 创建表格\n",
    "conn = pymysql.connect(host= '127.0.0.1',port=3306,user='root',\\\n",
    "                       password = '123456' ,database = 'test' ,charset='utf8')\n",
    "cur = conn.cursor() \n",
    "  # 返回游标对象\n",
    "sq2 = \"create table if not exists stock_abc (id int not null, date varchar(20), open varchar(100))\"\n",
    "cur.execute(sq2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "### 删除表格\n",
    "sq3 = \"drop table stock_abc\"\n",
    "cur.execute(sq3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "### 写入表格\n",
    "conn = pymysql.connect(host= '127.0.0.1',port=3306,user='root',\\\n",
    "                       password = '123456' ,database = 'test' ,charset='utf8')\n",
    "  # connect返回对象常用的方法\n",
    "  #   （1）cursor()，创建游标\n",
    "  #   （2）commit()，提交事务\n",
    "  #   （3）rollback()，回滚事务\n",
    "  #   （4）close()，关闭数据库连接\n",
    "cur = conn.cursor()\n",
    "sql = \"create table if not exists stock_abcd (id int not null unique auto_increment, date varchar(20), prices varchar(100), primary key(id))\"\n",
    "cur.execute(sql)\n",
    "sql =\"\"\"insert into stock_abcd (date, prices) values ('20200102', '12.3')\"\"\"\n",
    "try:\n",
    "    cur.execute(sql)\n",
    "    conn.commit()  \n",
    "       # connect返回对象常用的方法之一\n",
    "       #   commit()，提交事务\n",
    "except:\n",
    "    conn.rollback()\n",
    "       # connect返回对象常用的方法之一\n",
    "       #   rollback()，回滚事务\n",
    "\n",
    "conn.close()\n",
    "  # close()，关闭数据库连接"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Navicat （拓展阅读）\n",
    "\n",
    "Powerful database management & design GUI \n",
    "\n",
    "是一套多连接数据库开发工具，让你在单一应用程序中同时连接多达七种数据库：MySQL、MariaDB、MongoDB、SQL Server、SQLite、Oracle 和 PostgreSQL，可一次快速方便地访问所有数据库。\n",
    "\n",
    "https://baike.baidu.com/item/navicat/3089699?fr=ge_ala\n",
    "https://blog.csdn.net/qq_45069279/article/details/105919312"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### MongoDB（拓展阅读）\n",
    "\n",
    "`pip install pymongo`\n",
    " \n",
    "或者下载相应的wheel文件，tar.gz文件进行安装 \n",
    "\n",
    "- 安装wheel 先下载文件***.whl， 然后切换到该whl文件的目录。运行 pip install filename.whl \n",
    "- 安装tar.gz 先下载文件***.tar.gz, 然后将该文件解压到相应目录。运行 pip install 解压目录  注意该目录中应该有setup.py文件"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Database(MongoClient(host=['localhost:27017'], document_class=dict, tz_aware=False, connect=True), 'interlock')"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pymongo\n",
    " \n",
    "myclient = pymongo.MongoClient(\"mongodb://localhost:27017/\")\n",
    "mydb = myclient[\"interlock\"]\n",
    "\n",
    "mydb"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.15"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
